<?php

/*
 * This file is the ajax request for searching the documents
 * 
 * Created by: Peter Agno Jr.
 * Date created: September 22, 2011
 * 
 * In parameters : documentType, documentName, label, documentEvent
 * Out parameters : documentId, documentName, documentType, activity
 */

session_start();

    // Start - Check the permission of the user if it has VIEWALLDOCS
    function allDocsPerm($theUserId) {
        $query = 
        "
            SELECT *
            FROM Account_Dept_Pos as ADP, Dept_Pos_SecurityGroup as DPSG, SecurityGroup_Permission as SGP            
            WHERE ADP.userId = '$theUserId' AND ADP.deptPosId = DPSG.deptPosId AND DPSG.securityGroupId = SGP.securityGroupId AND SGP.permissionId = 'VIEWALLDOCS'
        ";

        $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());

        if ( mysql_fetch_array($result) != 0 ) {
            return true;
        }
        else {
            return false;
        }
    } // End - Check the permission of the user if it has VIEWALLDOCS

// Start - Checker for those users who will just go to the page by typing directly in the url.
if ($_POST) {
    // INCLUDES CONFIGURATION VARIABLES FOR DATABASE ACCESS
    include('../includes/siteConfig.php');
    
    // CONNECT TO DATABASE
    $connect = mysql_connect($hostName,$rootName,$dBasePassword) or die ('Unable to connect!');
    mysql_select_db($dBaseName) or die ('Unable to select database!');
    
    // GET VARIABLES VIA POST METHOD
    $documentType = $_POST['documentType'];
    $documentName = $_POST['documentName'];
    $documentId = $_POST['documentId'];
    $label = $_POST['label'];
    $documentEvent = $_POST['documentEvent'];
    
    // GET VARIABLES VIA SESSION METHOD
    $userId = $_SESSION['loggedUserId'];
    
    // PREPARE THE VARIABLEs FOR THE WHERE QUERY
    // FILTER THE DOCUMENT TYPE
    $filterTypeName = "";
    if( $documentType != 'All' ) {
        $filterTypeName = "WHERE type = '" . $documentType . "' ";
    }
    
    // FILTER THE DOCUMENT NAME
    if( $documentName != '' ) {
        if( $documentType == 'All' ) {
            $filterTypeName = $filterTypeName . "WHERE ";
        }
        else {
            $filterTypeName = $filterTypeName . "AND ";
        }
        
        $filterTypeName = $filterTypeName . "documentName LIKE '%" . $documentName . "%' ";
    }
    
    // FILTER THE ID
    $filterId = "";
    if( $documentId != '' ) {
        $filterId = "AND documentId LIKE '%" . $documentId . "%' ";
    }
    
    // FILTER THE LABEL
    $filterLabel = "";
    if( $label != '' ) {
        $filterLabel = "WHERE label LIKE '%" . $label . "%' ";
    }
    
    // Check permission
    // Filter user
    $filterUser = "";
    if ( allDocsPerm($userId) == false ) {
        $filterUser = "WHERE userId = '".$userId."' ";
    }
    
    // FILTER THE DOCUMENT EVENT
    $filterEvent = "";
    $groupByDoc = "";
    if( $documentEvent != 'All' ) {
        // Check if the first will be WHERE or AND
        if ( $filterUser == "" ) {
            $filterEvent = $filterEvent . "WHERE ";
        }
        else {
            $filterEvent = $filterEvent . "AND ";
        }
        
        $filterEvent = $filterEvent . "activity LIKE '%" . $documentEvent . "%' ";
        $groupByDoc = "GROUP BY documentId";
    }
    else {
//        $groupByDoc = "GROUP BY documentId, activity"; // Will also group the event
        $groupByDoc = "GROUP BY documentId";
    }
    
    // QUERY - GET ALL DOCUMENTS ASSOCIATED WITH USER'S INPUTS
    $query = 
        "
            -- Filter activity
            SELECT *
            FROM
            
                -- Connect activity
                (SELECT userId, documentSetupId, type, documentName, FilteredLabel.documentId as documentId, label, activity
                FROM

                    -- Filter label
                    (SELECT *
                    FROM

                        -- Connect label
                        (SELECT documentSetupId, type, documentName, documentId, label
                        FROM 
                        
                            -- Connect to Document_Label
                            (SELECT documentSetupId, type, documentName, WithDocId.documentId as documentId, labelId
                            FROM

                                -- Get the document id
                                (SELECT DocTypeName.documentSetupId as documentSetupId, type, documentName, MainDocAttachUnion.documentId as documentId
                                FROM

                                    -- Filter document type and name
                                    (SELECT documentSetupId, type, documentName
                                    FROM DocumentSetup
                                    ".$filterTypeName.") as DocTypeName,

                                    -- Union MainDocument and Attachment
                                    ((SELECT mainDocumentId as documentId, documentSetupId
                                    FROM MainDocument)
                                    UNION
                                    (SELECT attachmentId as documentId, documentSetupId
                                    FROM Attachment)) as MainDocAttachUnion

                                WHERE DocTypeName.documentSetupId = MainDocAttachUnion.documentSetupId ".$filterId.") as WithDocId
                                
                            LEFT JOIN Document_Label
                            ON WithDocId.documentId = Document_Label.documentId) as WithLabelId

                        LEFT JOIN Label
                        ON WithLabelId.labelId = Label.labelId) as UnFilteredLabel

                    ".$filterLabel.") as FilteredLabel

                LEFT JOIN Log
                ON FilteredLabel.documentId = Log.documentId) as  UnFilteredLog
            
            ".$filterUser." ".$filterEvent."
                
            ".$groupByDoc."
                
            ORDER BY documentId
        ";
    
    $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
    
    //THIS IDENTIFIES IF THERE EXIST DOCUMENTS.
    if ( mysql_num_rows($result) > 0 ) {
        // SAVE QUERY RESULTS TO AN ARRAY > $row   
        while ($row = mysql_fetch_array($result)) {
            // USER CAN VIEW THE DOCUMENT IF HE/SHE IS THE ORIGIN, OR IT PASSED BY TO HIM/HER

            // CHECK IF THE DOCUMENT HAD CONNECTION WITH THE USER
//            $query = 
//                "
//                    SELECT * 
//                    FROM Log
//                    WHERE documentId = '" . $row['documentId'] . "' AND userId = '$userId'
//                ";
//
//            $docResult = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
//
//            if (mysql_num_rows($docResult) > 0) {
                $jsondata = array();
                $jsondata['documentId'] = $row['documentId'];
                $jsondata['documentName'] = $row['documentName'];
                $jsondata['documentType'] = $row['type']; 
                $jsondata['activity'] = $row['activity'];
                $feed[] = $jsondata;
//            }
//            mysql_free_result($docResult);
        }

        if ( !empty($feed) ) 
            echo json_encode($feed);
        else
            echo json_encode(0);
    }
    else {
        echo json_encode(0);
    }
    
    mysql_free_result($result);
    mysql_close($connect);
}   // End - Checker for those users who will just go to the page by typing directly in the url.
else {
 echo "You are not authorized to view this page. This incident will be reported immediately.";
}
?>
